package com.wxtf.platform.main.sygl;

import java.io.File;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.FormatUtil;
import com.wxtf.platform.tools.XLSUtils;

/**
 * <li>Title: noBirth_Action.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.sygl</li>
 * <li>Description: 无生育条件登记信息</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Aug 21, 2012 10:29:02 AM</li>
 * 
 * @author wangli
 * @version 1.0.0.0
 * 
 */
public class noBirth_recordAction extends LogicBase implements
		CommonsCallProvider {
	private static final Log log = LogFactory
			.getLog(noBirth_recordAction.class);

	public noBirth_recordAction(String jsonArray) {

	}

	@Override
	public Object run() {
		Object msg = "";
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		try {
			if ("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
				return msg;
			} else if ("save".equals(this.getMethodName())) {
				msg = this.insert(this.getLogicData());
			} else if ("update".equals(this.getMethodName())) {
				msg = this.update(this.getLogicData());
			} else if ("delete".equals(this.getMethodName())) {
				msg = this.delete(this.getLogicData());
			} else if ("expData".equals(this.getMethodName())) {
				msg = this.expData(this.getLogicData());
			}else{
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(user.getUserrelName(), e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	String[] fieldss = { "tbe_is_barrenness", "the_is_menopause",
			"tbenhbcr_id", "org_id", "tbe_id", "tbenhbcr_name", "tbenhbcr_sex",
			"tbenhbcr_age", "tbenhbcr_idcard", "tbenhbcr_spouse_name",
			"tbenhbcr_spouse_sex", "tbenhbcr_spouse_age",
			"tbenhbcr_spouse_idcard", "tbenhbcr_marital_status",
			"tbenhbcr_marital_condition", "tbenhbcr_reason", "tbenhbcr_memo",
			"reserve1", "reserve2", "reserve3", "reserve4", "reserve5",
			"create_by", "create_time", "last_modify_by", "last_modify_time" };

	String[] fields = { "tbenhbcr_id", "org_id", "tbe_id", "tbenhbcr_name",
			"tbenhbcr_sex", "tbenhbcr_age", "tbenhbcr_idcard",
			"tbenhbcr_spouse_name", "tbenhbcr_spouse_sex",
			"tbenhbcr_spouse_age", "tbenhbcr_spouse_idcard",
			"tbenhbcr_marital_status", "tbenhbcr_marital_condition",
			"tbenhbcr_reason", "tbenhbcr_memo", "reserve1", "reserve2",
			"reserve3", "reserve4", "reserve5", "create_by", "create_time",
			"last_modify_by", "last_modify_time" };

	@Override
	public Object select(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute(
				"userBean");

		// 已婚育龄无生育男【离异，丧偶，不孕症】妻子年龄20-49
		sql = " FROM(SELECT e.*,h.tbenhbcr_id,h.tbenhbcr_reason,h.tbenhbcr_memo FROM tb_employee e LEFT JOIN tb_family_base f ON e.tbe_id=f.tbe_id LEFT JOIN tbe_not_have_birth_con_record h ON e.tbe_id=h.tbe_id where"
				+ " (tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323') or (((f.tbfi_spouse_birthday <= DATE_SUB(curdate(),INTERVAL 20 YEAR) and f.tbfi_spouse_birthday >= DATE_SUB(curdate(),INTERVAL 49 YEAR)) or f.tbfi_spouse_birthday is null) and tbe_is_barrenness='1'))"
				+ " and tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320') and tbe_status not in('102-5','102-4','102-3')"
				+ " and e.tbe_employment_forms <> '31' and e.tbe_sex='男' "
				// 已婚育龄无生育女
				+ " UNION "
				+ " SELECT e.*,h.tbenhbcr_id,h.tbenhbcr_reason,h.tbenhbcr_memo FROM tb_employee e LEFT JOIN tbe_not_have_birth_con_record h ON e.tbe_id=h.tbe_id where e.tbe_id not in (SELECT tbe_id from tb_employee where tbe_is_barrenness='0' and the_is_menopause='0'"
				+ " and tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617')) and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 20 YEAR) and tbe_birthday >= DATE_SUB(curdate(),INTERVAL 49 YEAR) "
				+ " and tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320') and tbe_status not in('102-5','102-4','102-3')"
				+ " and tbe_employment_forms <> '31' and tbe_sex='女')e where 1=1 ";

		// 从职工表中查询符合无生育条件的男女职工
		JSONArray array = JSONArray.fromObject(jsonArray);
		if (array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String types = jsonObject.getString("types");
			if ("init".equals(types)) {// 初始查询
				sql += " and e.org_id in (" + user.getCurSubOrgId() + ")";
			} else if ("serch1".equals(types)) {// 条件查询
				String orgIds = jsonObject.getString("orgIds");// 机构ID串

				String tbenhbcr_idcard = jsonObject
						.getString("tbe_work_permit");// 职工号
				String tbe_name = jsonObject.getString("tbe_name");// 姓名
				String tbenhbcr_reason = jsonObject.getString("tbe_spell_code");// 原因

				StringBuffer buff = new StringBuffer();
				if (StringUtils.isNotBlank(orgIds)) {
					buff.append(" and e.org_id in (").append(
							user.getCurSubOrgIds(orgIds)).append(")");
				}

				if (StringUtils.isNotBlank(tbenhbcr_idcard)) {
					buff.append(" and tbe_idcard like '%").append(
							tbenhbcr_idcard).append("%'");
				}
				if (StringUtils.isNotBlank(tbe_name)) {
					buff.append(" and tbe_name like '%").append(tbe_name)
							.append("%'");
				}
				// 无生育条件原因
				if (StringUtils.isNotBlank(tbenhbcr_reason)) {
					if (!tbenhbcr_reason.equals("0") && tbenhbcr_reason != "") {
						buff.append(" and tbenhbcr_reason like '%").append(
								tbenhbcr_reason.toUpperCase()).append("%'");
					}
				}

				sql += buff.toString();
			} else if ("serch2".equals(types)) {// 高级查询
				String orgIds = jsonObject.getString("orgIds");// 机构ID串
				String where = jsonObject.getString("where");// 机构ID串
				sql += " and org_id in (" + user.getCurSubOrgIds(orgIds) + ") "
						+ where;
			}
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if (sortname != null && sortname != null) {
			sql += " order by " + sortname + " " + sortorder;
		}
		
		String countSql = "select count(*) " + sql;
		String querySql = " SELECT tbe_is_barrenness,the_is_menopause,tbenhbcr_id,org_id,e.tbe_id,tbe_name as tbenhbcr_name,"
				+ "tbe_sex as tbenhbcr_sex,tbe_age as tbenhbcr_age,tbe_idcard as tbenhbcr_idcard,'0' as tbenhbcr_spouse_name,'0' as tbenhbcr_spouse_sex,'0' as tbenhbcr_spouse_age,'0' as tbenhbcr_spouse_idcard,"
				+ "tbe_marital_status as tbenhbcr_marital_status,tbe_marital_condition as tbenhbcr_marital_condition,tbenhbcr_reason,tbenhbcr_memo,reserve1,reserve2,reserve3,reserve4,reserve5,create_by,create_time,last_modify_by,last_modify_time "
				+ sql;
		String strJson = AccessDataBaseUtil.query4SQLPagging(fieldss, countSql, querySql, this.getPage(),this.getPagesize(), null);
		//System.out.println("querySql:" + querySql);
		int emp = 0;
		int memp = 0;
		int femp = 0;
		String man ="select count(*) "+sql+" and tbe_sex = '男'";
		String fel ="select count(*) "+sql+" and tbe_sex = '女'";
		memp=AccessDataBaseUtil.queryForInt(man, null);
		femp=AccessDataBaseUtil.queryForInt(fel, null);
		emp=memp+femp;
		return strJson.substring(0, strJson.indexOf(","))+",\"emp\":\"" + emp+ "\",\"memp\":\"" + memp + "\","+ "\"femp\":\"" +femp+ "\""+ strJson.substring(strJson.indexOf(","), strJson.length());
	}

	@Override
	public Object insert(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute(
				"userBean");

		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));

		StringBuffer buff = new StringBuffer();
		StringBuffer buff2 = new StringBuffer();
		buff.append("insert into tbe_not_have_birth_con_record(tbenhbcr_id");
		Object[] obj = new Object[fields.length];

		String a = jsonObject.getString("tbe_is_barrenness");
		String b = jsonObject.getString("the_is_menopause");

		for (int j = 1; j < fields.length; j++) {
			obj[j] = jsonObject.getString(fields[j]);
			buff.append(",").append(fields[j]);
			if (j == 13) {
				if (a.equals("1")) {
					obj[13] = (obj[13].equals("")) ? "不孕症" : obj[13] + ","
							+ "不孕症";
				}
				if (b.equals("1")) {
					obj[13] = (obj[13].equals("")) ? "绝经" : obj[13] + ","
							+ "绝经";
				}
			}
		}
		buff.append(") values(");
		obj[0] = AccessDataBaseUtil.getPKUUID();
		String curTime = FormatUtil.getCurentDateTime();
		obj[20] = user.getUserId();
		obj[21] = curTime;
		obj[22] = user.getUserId();
		obj[23] = curTime;

		for (int i = 0; i < obj.length; i++) {
			buff2.append("'").append(obj[i]).append("',");
		}
		buff2.deleteCharAt(buff2.length() - 1);
		String sql1 = buff.append(buff2.toString()).append(")").toString();

		String stremp = "  update tb_employee set tbe_is_barrenness = '" + a
				+ "',the_is_menopause = '" + b + "',tbe_marital_status='"
				+ obj[11] + "' where tbe_id ='"
				+ jsonObject.getString("tbe_id") + "'";
		String[] sqls;
		if ("".equals(obj[13])) {
			sqls = new String[] { stremp };// 当职工直接修改到健康状态下，该条数据不用插入
		} else {
			sqls = new String[] { sql1, stremp };
		}

		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"保存成功\"";
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute(
				"userBean");
		String curTime = FormatUtil.getCurentDateTime();

		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		StringBuffer buff = new StringBuffer();
		buff.append("update tbe_not_have_birth_con_record set ");
		Object[] obj = new Object[fields.length - 1];

		String a = jsonObject.getString("tbe_is_barrenness");
		String b = jsonObject.getString("the_is_menopause");

		for (int j = 1; j < fields.length - 1; j++) {
			obj[j - 1] = jsonObject.getString(fields[j]);
			obj[21] = user.getUserId();
			obj[22] = curTime;
			if (j == 13) {
				if (a.equals("1")) {
					obj[12] = (obj[12].equals("")) ? "不孕症" : obj[12] + ","
							+ "不孕症";
				}
				if (b.equals("1")) {
					obj[12] = (obj[12].equals("")) ? "绝经" : obj[12] + ","
							+ "绝经";
				}
			}
			buff.append(fields[j]).append(" = ").append("'").append(obj[j - 1])
					.append("',");
		}
		buff.deleteCharAt(buff.length() - 1).append(
				" where tbenhbcr_id = '" + jsonObject.getString("tbenhbcr_id")
						+ "'");
		String strUpdate = buff.toString();
		// 当职工所有的无生育条件都消失的情况下，该条数据直接删除，不做修改
		if (obj[12].equals("")) {
			strUpdate = "delete from tbe_not_have_birth_con_record where tbenhbcr_id ='"
					+ jsonObject.getString("tbenhbcr_id") + "'";
		}
		String s = "  update tb_employee set tbe_is_barrenness = '" + a
				+ "',the_is_menopause = '" + b + "',tbe_marital_status='"
				+ obj[10] + "' where tbe_id ='" + obj[1] + "'";

		String[] sqls = { strUpdate, s };
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"修改成功\"";
	}

	@Override
	public Object delete(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String id = jsonObject.getString("ids");
		String[] ids = id.split(",");
		sql = "delete from tbe_not_have_birth_con_record where tbenhbcr_id = ?";

		AccessDataBaseUtil.deleteByIds(sql, ids);
		return "\"删除成功\"";
	}

	/**
	 * 描述：expData 人员导出
	 * 
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn
	 * @author wangli
	 */
	public Object expData(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		StringBuffer buff = new StringBuffer();
		// 已婚育龄无生育男【离异，丧偶，不孕症】妻子年龄20-49
		buff.append(" select reserve1,tbe_name,tbe_sex,tbe_idcard,tbe_birthday,(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = tbe_marital_status),tbenhbcr_reason,tbenhbcr_memo FROM(SELECT e.*,h.tbenhbcr_id,h.tbenhbcr_reason,h.tbenhbcr_memo FROM tb_employee e LEFT JOIN tb_family_base f ON e.tbe_id=f.tbe_id LEFT JOIN tbe_not_have_birth_con_record h ON e.tbe_id=h.tbe_id where")
			.append(" (tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323') or (((f.tbfi_spouse_birthday <= DATE_SUB(curdate(),INTERVAL 20 YEAR) and f.tbfi_spouse_birthday >= DATE_SUB(curdate(),INTERVAL 49 YEAR)) or f.tbfi_spouse_birthday is null) and tbe_is_barrenness='1'))")
			.append(" and tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320') and tbe_status not in('102-5','102-4','102-3')")
			.append(" and e.tbe_employment_forms <> '31' and e.tbe_sex='男' ")
				// 已婚育龄无生育女
			.append(" UNION ")
			.append(" SELECT e.*,h.tbenhbcr_id,h.tbenhbcr_reason,h.tbenhbcr_memo FROM tb_employee e LEFT JOIN tbe_not_have_birth_con_record h ON e.tbe_id=h.tbe_id where e.tbe_id not in (SELECT tbe_id from tb_employee where tbe_is_barrenness='0' and the_is_menopause='0'")
			.append(" and tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617')) and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 20 YEAR) and tbe_birthday >= DATE_SUB(curdate(),INTERVAL 49 YEAR) ")
			.append(" and tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320') and tbe_status not in('102-5','102-4','102-3')")
			.append(" and tbe_employment_forms <> '31' and tbe_sex='女')e where 1=1 ");
		
		//String strexp = "select reserve1,tbe_name,tbe_sex,tbe_idcard,tbe_birthday,(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = tbe_marital_status),tbenhbcr_reason,tbenhbcr_memo"+sql;
		String orgIds = jsonObject.getString("orgIds");// 机构ID串
		String tbenhbcr_idcard = jsonObject.getString("tbe_work_permit");// 职工号
		String tbe_name = jsonObject.getString("tbe_name");// 姓名
		String tbenhbcr_reason = jsonObject.getString("tbe_spell_code");// 原因
		

		if (StringUtils.isNotBlank(orgIds)) {
			buff.append(" and e.org_id in (").append(
					user.getCurSubOrgIds(orgIds)).append(")");
		}

		if (StringUtils.isNotBlank(tbenhbcr_idcard)) {
			buff.append(" and tbe_idcard like '%").append(tbenhbcr_idcard)
					.append("%'");
		}
		if (StringUtils.isNotBlank(tbe_name)) {
			buff.append(" and tbe_name like '%").append(tbe_name).append("%'");
		}
		// 无生育条件原因
		if (StringUtils.isNotBlank(tbenhbcr_reason)) {
			if (!tbenhbcr_reason.equals("0") && tbenhbcr_reason != "") {
				buff.append(" and tbenhbcr_reason like '%").append(
						tbenhbcr_reason.toUpperCase()).append("%'");
			}
		}

		//strexp += buff.toString();
		//System.out.println("strexp:" + strexp);
		List<Object[]> list = AccessDataBaseUtil.query(buff.toString(), null);

		String url = this.getRequest().getSession().getServletContext()
				.getRealPath("/jsp/app/xls/");
		String model = url + "/model/not_have_birth.xls";
		String file = url + "/file/not_have_birth.xls";
		String rfile = "jsp/app/xls/file/not_have_birth.xls";
		File ffrom = new File(model);
		File fto = new File(file);
		Common.copyFile(ffrom, fto);

		if (list.size() > 0) {
			XLSUtils.writeXls(list, fto, "无生育状况一览表", 1);// 写xls内容
		} else {
			return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"导出结果集没有记录\"}";
		}

		return "\"" + rfile + "\"";
	}

	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
}